﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_GetStaffFromPosition_V2]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_GetStaffFromPosition_V2];
GO
CREATE PROCEDURE [dbo].[sproc_GetStaffFromPosition_V2]
    @StaffName nvarchar(300),
    @IncludePeers bit = 1,          -- 是否包含同级成员
    @NumLowerLevels int = 128       -- 指定包含几级下属
/*

============================================================
功能:    得到@UserName所在职位的成员或下级成员
参数:
    @UserName nvarchar(300) :    用户名
    @IncludePeers bit       :    是否包含同级成员
    @NumLowerLevels int     :    指定包含几级下属


============================================================

*/
AS
BEGIN
SET NOCOUNT ON;

DECLARE @Position_ID int;

--得到职位的成员ID
SELECT @Position_ID = SIP.Position_id 
    FROM dbo.UDS_Staff S INNER JOIN dbo.UDS_Staff_in_position SIP ON S.staff_id = SIP.staff_id
    WHERE S.staff_name = @staffname;
IF ( @@ROWCOUNT <> 1 )
BEGIN
    RAISERROR(N'The specified user "%s" does not exist.', 16, 1, @StaffName);
    RETURN -1;
END

exec dbo.[sproc_GetStaffByPosition_V2]
    @PositionID = @Position_ID,
    @IncludePeers = @IncludePeers,
    @NumLowerLevels = @NumLowerLevels;

END
GO

/*
 * Below is the original implementation:
 */
/*
CREATE PROCEDURE [dbo].[sproc_GetStaffFromPosition] 
    @StaffName nvarchar(300),
    @Inherit int =3,
    @Upsides int =3

============================================================
功能:    得到@UserName所在职位的成员或下级成员
参数:
    @UserName nvarchar(300)    :    用户名
    @Inherit int =0        :    继承关系
    @Upsides int =0        :    是否包含同级成员


============================================================

AS
SET NOCOUNT ON
DECLARE @Position_ID int

SET @Position_id =0
--得到职位的成员ID
SELECT @Position_ID = b.Position_id 
    FROM 
        uds_staff a,
        uds_staff_in_position b
    WHERE       a.staff_name = @staffname 
        and a.staff_id = b.staff_id

BEGIN
CREATE TABLE #TabStaff (staff_id int)
--加入同级成员
IF @Upsides & 1 = 1
    INSERT INTO #TabStaff (staff_id)
        SELECT staff_id 
            FROM 
                uds_staff_in_position
            WHERE Position_id = @Position_ID
--下级成员
IF @Upsides & 2 = 2
BEGIN
    --只加入直属下级成员
    IF @Inherit & 1 =1 
        INSERT INTO #TabStaff (staff_id)
            SELECT staff_id FROM uds_staff_in_position
            WHERE Position_id in (
                        SELECT Position_id 
                            FROM 
                                uds_Position 
                            WHERE super_Position_id = @Position_id
                              and super_Position_id <>Position_id 
                          )
    --加入递归多层下级成员
    IF @Inherit & 2 =2
    BEGIN
        DECLARE @ids nvarchar(2000)
        DECLARE @tmpids nvarchar(2000)
        DECLARE @oldids nvarchar(2000)
        
        SET @ids =''
        SET @tmpids=''
        SET @oldids =''
        
        SELECT @ids = @ids + convert(nvarchar,Position_id) + ',' 
            FROM 
                uds_Position 
            WHERE         super_Position_id = @Position_id  
                and super_Position_id <>Position_id 
        IF LEN(@ids)>0
            SET @ids = LEFT(@ids,LEN(@ids)-1)
        
        CREATE TABLE #Position(id int)
        SET @oldids = @ids
        
        WHILE LEN(@oldids)>0
        BEGIN
            
            EXEC ('INSERT INTO #Position 
                    SELECT Position_id
                        FROM 
                            uds_Position 
                        WHERE super_Position_id IN (' + @oldids +')')
            SET @tmpids=''
            SELECT @tmpids = @tmpids + convert(nvarchar,id) + ',' 
                FROM #Position

            IF LEN(@tmpids)>0
                SET @tmpids = LEFT(@tmpids,LEN(@tmpids)-1)
        
            SET @oldids = @tmpids
            DELETE FROM #Position
            SET @ids = @ids + ',' + @tmpids
        END
        DROP TABLE #Position

        IF RIGHT(@ids,1)=','
            SET @ids = LEFT(@ids,LEN(@ids)-1)
        PRINT 'INSERT INTO #TabStaff (staff_id)
                SELECT staff_id 
                    FROM 
                        uds_staff_in_position 
                    WHERE Position_id IN (' + @ids + ')'
        if LEN(@ids)>0
        EXEC('INSERT INTO #TabStaff (staff_id)
                SELECT staff_id 
                    FROM 
                        uds_staff_in_position 
                    WHERE Position_id IN (' + @ids + ')')
        
    END

END
--得到结果
SELECT * 
    FROM uds_staff 
    WHERE staff_id IN (
                SELECT staff_id 
                    FROM #tabStaff
               )
        AND dimission = 0
DROP TABLE #tabStaff
END


SET NOCOUNT OFF
*/